package com.managementSystem.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.managementSystem.client.StaffBeanService;
import com.managementSystem.shared.StaffBean;

@SuppressWarnings("serial")
public class StaffBeanServiceImpl extends RemoteServiceServlet implements StaffBeanService {
	public StaffBean authenticateUser (String username,String password) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			System.out.println("hARi rules all");
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("select * from Staff where Username like binary '" + username + "' and Password like binary '" + password + "';");
			ResultSet result = ps.executeQuery();
			StaffBean staff = new StaffBean();
			System.out.println("Made it here");
			System.out.println(username+" "+password);
			
			while (result.next()) {
				staff=createBean(result);
				System.out.println(staff.getId());
				HttpServletRequest request = this.getThreadLocalRequest();
				HttpSession session = request.getSession();
				session.setAttribute("user", staff);
				return staff;
			}
			result.close();
			ps.close();
			
		} catch (Exception e) {

		}
		return null;
	}
	
	public List<StaffBean> simpleSearch(String search) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";
		List<StaffBean> results=null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("select * from Staff where ID like '" + search + "';");
			ResultSet result = ps.executeQuery();
			StaffBean staff = new StaffBean();
			results=new ArrayList<StaffBean>();
			while (result.next()) {
				staff=createBean(result);
				results.add(staff);
				System.out.println(staff.getId());
			}
			result.close();
			ps.close();
			
			ps = con.prepareStatement ("select * from Staff where Username like '" + search + "';");
			result = ps.executeQuery();
			staff = new StaffBean();
			int add=1;
			while (result.next()) {
				staff=createBean(result);
				for (int i=0; i<results.size();i++) {
					if (results.get(i).getId().equals(staff.getId())) {
						add=0;
					}
				}
				if (add==1)
					results.add(staff);
			}
			result.close();
			ps.close();
			ps = con.prepareStatement ("select * from Staff where Firstname like '" + search + "';");
			result = ps.executeQuery();
			staff = new StaffBean();
			add=1;
			while (result.next()) {
				staff=createBean(result);
				for (int i=0; i<results.size();i++) {
					if (results.get(i).getId().equals(staff.getId())) {
						add=0;
					}
				}
				if (add==1)
					results.add(staff);
			}
			result.close();
			ps.close();
			ps = con.prepareStatement ("select * from Staff where Lastname like '" + search + "';");
			result = ps.executeQuery();
			staff = new StaffBean();
			add=1;
			while (result.next()) {
				staff=createBean(result);
				for (int i=0; i<results.size();i++) {
					if (results.get(i).getId().equals(staff.getId())) {
						add=0;
					}
				}
				if (add==1)
					results.add(staff);
			}
			result.close();
			ps.close();
			ps = con.prepareStatement ("select * from Staff where Email like '" + search + "';");
			result = ps.executeQuery();
			staff = new StaffBean();
			add=1;
			while (result.next()) {
				staff=createBean(result);
				for (int i=0; i<results.size();i++) {
					if (results.get(i).getId().equals(staff.getId())) {
						add=0;
					}
				}
				if (add==1)
					results.add(staff);
			}
			result.close();
			ps.close();
			return results;
			
		} catch (Exception e) {
			System.out.println("Search failed");
		}
		return null;
	}
	
	public StaffBean getStaff(String ID) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("select * from Staff where ID like '" + ID + "';");
			ResultSet result = ps.executeQuery();
			StaffBean approve=null;
			while (result.next()) {
				approve = new StaffBean();
				approve=createBean(result);
			}
			
			result.close();
			ps.close();
			return approve;
			
		} catch (Exception e) {

		}
		return null;
	}
	
	private static StaffBean createBean(ResultSet result) throws SQLException {
		StaffBean user=new StaffBean();
		user.setId(result.getString("ID"));
		user.setFirstName(result.getString("Firstname"));
		user.setLastName(result.getString("Lastname"));
		user.setUserName(result.getString("Username"));
		user.setPassword(result.getString("Password"));
		user.setEmail(result.getString("Email"));
		user.setPrimaryNumber(result.getString("PrimaryNumber"));
		user.setSecondaryNumber(result.getString("SecondaryNumber"));
		user.setPriority(result.getInt("Priority"));
		user.setWarnings(result.getInt("Warnings"));
		user.setType(result.getInt("Type"));
		user.setNumDaysAbsent(result.getInt("DaysAbsent"));
		user.setNumDaysLate(result.getInt("DaysLate"));
		return user;
	}
	
	public StaffBean getSessionStaff(String s) {
		HttpServletRequest request = this.getThreadLocalRequest();
		HttpSession session = request.getSession();
		StaffBean b = (StaffBean)session.getAttribute(s);
		return b;
	}
	
}
